﻿using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Lists;
using System.Data.SqlClient;
using GeneralLibs;
using Excel = Microsoft.Office.Interop.Excel;
using System.Collections.Generic;

namespace BasicForms
{
    public partial class FrmTinhGiaVonExportExcel : BasicForms.FrmOKCancel
    {
        public FrmTinhGiaVonExportExcel()
        {
            InitializeComponent();
        }

        private void FrmTinhGiaVonExportExcel_Load(object sender, EventArgs e)
        {
            if (DesignMode) return;

            cmbKho.DataSource = DanhMucKho.Instance.DataView;
            cmbKho.DisplayColumn = "Ten";
            cmbKho.ValueColumn = "ID";
            cmbKho.AddVisibleColumn("Ma", "Mã", 100, 1);
            cmbKho.AddVisibleColumn("Ten", "Tên", 300, 2);

            cmbThoiDiemKhoaSoTinhGiaVon.DataSource = DanhMucThoiDiemKhoaSo.GetCacThoiDiemChotSoDu().DataView;
            cmbThoiDiemKhoaSoTinhGiaVon.DisplayColumn = "ThoiDiem";
            cmbThoiDiemKhoaSoTinhGiaVon.ValueColumn = "ID";
            cmbThoiDiemKhoaSoTinhGiaVon.AddVisibleColumn("ThoiDiem", "Thời điểm");
            cmbThoiDiemKhoaSoTinhGiaVon.AddVisibleColumn("DienGiai", "Diễn giải");

        }

        #region FileName property
        public string FileName
        {
            get
            {
                return txtFileName.Text;
            }
        }
        #endregion

        #region KhoID property
        public Guid KhoID
        {
            get
            {
                if (cmbKho.EditValue != null && (Guid)cmbKho.EditValue != Guid.Empty)
                    return (Guid)cmbKho.EditValue;
                else
                    return Guid.Empty;
            }
        }
        #endregion

        #region ThoiDiemKhoaSo property
        public ThoiDiemKhoaSo ThoiDiemKhoaSo
        {
            get
            {
                if (cmbThoiDiemKhoaSoTinhGiaVon.EditValue != null && (Guid)cmbThoiDiemKhoaSoTinhGiaVon.EditValue != Guid.Empty)
                    return DanhMucThoiDiemKhoaSo.Instance.GetByID((Guid)cmbThoiDiemKhoaSoTinhGiaVon.EditValue);
                else
                    return null;
            }
        }
        #endregion

        private void btnImportFromTextFile_Click(object sender, EventArgs e)
        {
            openFileDialog.Filter = "Excel file|*.xlsx;*.xls";
            openFileDialog.InitialDirectory = "C:";
            openFileDialog.Title = "Chọn file excel đúng định dạng";
            DialogResult result = openFileDialog.ShowDialog();

            if (result == DialogResult.OK)
            {
                txtFileName.Text = openFileDialog.FileName;
            }
        }

        private void btnOK_Click_1(object sender, EventArgs e)
        {
            BasicForms.FrmProgressBar.StartShowProgress();
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);

            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand(); cmd.CommandTimeout = Config.TimeOut;
                DataTable dtResult = InitTable();

                string vatTuHangHoaCondition = "1=1";

                //đọc từ excel nếu có
                if (FileName != "")
                {
                    Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();
                    Excel.Workbook workBook = app.Workbooks.Open(FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t",
                                    false, false, 0, true, 1, 0);
                    Excel.Worksheet workSheet = (Excel.Worksheet)workBook.ActiveSheet;
                    Excel.Range range = workSheet.UsedRange;

                    if (range.Columns.Count >= 2)
                    {
                        vatTuHangHoaCondition = "Ma IN ("; //Điều kiện 

                        Hashtable htChungTuKhongHopLe = new Hashtable();
                        for (int rowIndex = 1; rowIndex <= range.Rows.Count; rowIndex++)
                        {
                            try
                            {
                                //Đọc từ cột 2, cột 1 STT
                                vatTuHangHoaCondition += string.Format("'{0}'", (range.Cells[rowIndex, 2] as Excel.Range).Text.ToString().Trim()) + ", ";
                            }
                            catch (Exception ex)
                            {
                                htChungTuKhongHopLe.Add(rowIndex, (range.Cells[rowIndex, 2] as Excel.Range).Text.ToString().Trim());
                            }
                        }

                        if (vatTuHangHoaCondition.EndsWith(", "))
                            vatTuHangHoaCondition = vatTuHangHoaCondition.Substring(0, vatTuHangHoaCondition.Length - 2) + ")";

                        workBook.Close(false, FileName, true);
                    }
                }

                DateTime now = DateTime.Now;

                DataTable dtVatTuHangHoa = new DataTable();
                cmd.CommandText = string.Format("SELECT * FROM VatTuHangHoa WHERE {0}", vatTuHangHoaCondition);
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dtVatTuHangHoa);

                dtResult.Clear();
                int count = 0;

                if (KhoID != null && KhoID != Guid.Empty)
                {
                    foreach (DataRow row in dtVatTuHangHoa.Rows)
                    {
                        BasicForms.FrmProgressBar.Status = string.Format("Đang xử lý {0}/{1}", count, dtVatTuHangHoa.Rows.Count);
                        count++;

                        Guid vatTuHangHoaID = (Guid)row["ID"];

                        VatTuHangHoa vatTuHangHoa = DanhMucVatTuHangHoa.Instance.GetByID(vatTuHangHoaID);

                        decimal donGiaVon = 0;
                        if (Config.PhuongPhapTinhGiaTonKho == PhuongPhapTinhGiaTonKho.BinhQuanGiaNhap)
                        {
                            donGiaVon = DanhMucVatTuHangHoa.Instance.GetGiaVonMuaTrungBinhToanBoTheoKhoByID(vatTuHangHoa.ID, KhoID,
                                ThoiDiemKhoaSo.ID, ThoiDiemKhoaSo.ThoiDiem, now);
                        }
                        else
                        {
                            donGiaVon = DanhMucVatTuHangHoa.Instance.GetGiaVonMuaTrungBinhToanBoTheoKhoByID(vatTuHangHoa.ID, KhoID,
                                ThoiDiemKhoaSo.ID, ThoiDiemKhoaSo.ThoiDiem, now);

                            //Nếu = 0 thì lấy thử toàn bộ các kho
                            if (donGiaVon <= 0)
                                donGiaVon = DanhMucVatTuHangHoa.Instance.GetGiaVonMuaTrungBinhToanBoCacKhoByID(vatTuHangHoa.ID, 
                                    ThoiDiemKhoaSo.ID, ThoiDiemKhoaSo.ThoiDiem, now);

                            //Nếu tiếp tục = 0 thì lấy giá vốn nhập trung bình, theo kho 
                             if (donGiaVon <= 0)
                                donGiaVon = DanhMucVatTuHangHoa.Instance.GetGiaVonNhapTrungBinhToanBoTheoKhoByID(vatTuHangHoa.ID, KhoID,
                                ThoiDiemKhoaSo.ID, ThoiDiemKhoaSo.ThoiDiem, now);

                             //Nếu tiếp tục = 0 thì lấy giá vốn nhập trung bình toàn bộ các kho 

                             if (donGiaVon <= 0)
                                 donGiaVon = DanhMucVatTuHangHoa.Instance.GetGiaVonNhapTrungBinhToanBoTheoKhoByID(vatTuHangHoa.ID, KhoID,
                                 ThoiDiemKhoaSo.ID, ThoiDiemKhoaSo.ThoiDiem, now);
                        }

                        DataRow addedRow = dtResult.NewRow();
                        addedRow["Ma"] = vatTuHangHoa.Ma;
                        addedRow["GiaVon"] = donGiaVon;
                        dtResult.Rows.Add(addedRow);
                    }
                }
                else
                {
                    foreach (DataRow row in dtVatTuHangHoa.Rows)
                    {
                        BasicForms.FrmProgressBar.Status = string.Format("Đang xử lý {0}/{1}", count, dtVatTuHangHoa.Rows.Count);
                        count++;

                        Guid vatTuHangHoaID = (Guid)row["ID"];

                        VatTuHangHoa vatTuHangHoa = DanhMucVatTuHangHoa.Instance.GetByID(vatTuHangHoaID);

                        decimal donGiaVon = 0;
                        if (Config.PhuongPhapTinhGiaTonKho == PhuongPhapTinhGiaTonKho.BinhQuanGiaNhap)
                        {
                            donGiaVon = DanhMucVatTuHangHoa.Instance.GetGiaVonNhapTrungBinhToanBoCacKhoByID(vatTuHangHoa.ID,
                                ThoiDiemKhoaSo.ID, ThoiDiemKhoaSo.ThoiDiem, now);
                        }
                        else
                        {
                            donGiaVon = DanhMucVatTuHangHoa.Instance.GetGiaVonMuaTrungBinhToanBoCacKhoByID(vatTuHangHoa.ID,
                                ThoiDiemKhoaSo.ID, ThoiDiemKhoaSo.ThoiDiem, now);

                            if (donGiaVon <= 0)
                                donGiaVon = DanhMucVatTuHangHoa.Instance.GetGiaVonNhapTrungBinhToanBoCacKhoByID(vatTuHangHoa.ID,
                                ThoiDiemKhoaSo.ID, ThoiDiemKhoaSo.ThoiDiem, now);
                        }

                        DataRow addedRow = dtResult.NewRow();
                        addedRow["Ma"] = vatTuHangHoa.Ma;
                        addedRow["GiaVon"] = donGiaVon;
                        dtResult.Rows.Add(addedRow);
                    }
                }

                try
                {
                    BasicForms.FrmProgressBar.StartShowProgress();
                    ExportToExcel.ExportDataToExcel(dtResult);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    BasicForms.FrmProgressBar.FinishShowProgress();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                con.Close();
                con = null;
                BasicForms.FrmProgressBar.FinishShowProgress();
            }
        }

        DataTable InitTable()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Ma", typeof(string));
            dt.Columns.Add("GiaVon", typeof(decimal));
            return dt;
        }
    }
}
